package com.scuec.groupfive.mapper;

import com.github.pagehelper.Page;
import com.scuec.groupfive.entity.PastExam;
import com.scuec.groupfive.vo.PastExamVO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

@Mapper
public interface PastExamMapper {

    // 修改：所有字段添加AS别名
    @Select("SELECT " +
            "record_id AS recordId, " +
            "user_id AS userId, " +
            "username, " +
            "exam_type AS examType, " +
            "exam_year AS examYear, " +
            "exam_date AS examDate, " +
            "total_questions AS totalQuestions, " +
            "correct_questions AS correctQuestions, " +
            "score, " +
            "total_time AS totalTime, " +
            "created_at AS createdAt " +
            "FROM past_exam_records ORDER BY record_id DESC")
    List<PastExam> getAllPastExams();

    // 修改：所有字段添加AS别名
    @Select("SELECT " +
            "per.record_id AS recordId, " +
            "per.user_id AS userId, " +
            "per.username, " +
            "per.exam_type AS examType, " +
            "per.exam_year AS examYear, " +
            "per.exam_date AS examDate, " +
            "per.total_questions AS totalQuestions, " +
            "per.correct_questions AS correctQuestions, " +
            "per.score, " +
            "per.total_time AS totalTime, " +
            "per.created_at AS createdAt, " +
            "ea.association_type AS associationType " + // 添加别名
            "FROM past_exam_records per " +
            "LEFT JOIN exam_association ea " +
            "ON per.record_id = ea.past_exam_id " +
            "AND per.user_id = ea.user_id " +
            "AND per.username = ea.username " +
            "WHERE per.username = #{username} " +
            "ORDER BY per.exam_date DESC")
    List<PastExamVO> getPastExamsByUsername(@Param("username") String username);

    // 新增：根据用户名查询统计信息
    @Select("SELECT " +
            "COUNT(*) AS examCount, " +
            "AVG(score) AS averageScore " +
            "FROM past_exam_records " +
            "WHERE username = #{username}")
    Map<String, Object> getExamStatsByUsername(@Param("username") String username);
}